package fr.epsi.BDD;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * Created by Florent Baudon on 15/05/2014.
 */
public class BDD {

    private BDDHelper helper;
    private SQLiteDatabase base;

    public void open(Context activity) throws SQLException {
        helper = new BDDHelper(activity);
        base = helper.getWritableDatabase();
    }

    public void closeBDD(){
        base.close();
    }

    public Cursor getEmployes(){

        return base.rawQuery("SELECT* FROM employes", null);
    }

    public Cursor getEmployeFiche(String nom, String prenom){
        return base.rawQuery("SELECT* FROM employes WHERE nom='" + nom + "' AND prenom='" + prenom + "'", null);
    }

    public void insertEmploye(String nom, String prenom, String contrat, String service, int tempstravail) throws SQLException{

        base.execSQL("INSERT INTO employes (nom, prenom, contrat, service,tempstravail) VALUES('" + nom + "', '" + prenom + "', '" + contrat + "', '" + service + "', "+ tempstravail + ")");

    }

    public void deleteEmploye(String nom, String prenom){
        base.execSQL("DELETE FROM employes WHERE nom='" + nom + "' AND prenom='" + prenom + "'");
    }

    public Cursor getServices(){
        return base.rawQuery("SELECT _id, nom FROM services;", null);
    }

    public Cursor getEmployesService(String nomService){
        return base.rawQuery("Select * FROM employes where service='" + nomService + "'", null);
    }

    public void insertTache(String label, String service, String nomEmp, String prenomEmp, String dateDeb, String dateFin){
        base.execSQL("INSERT INTO taches (nom, service, nomemploye, prenomemploye, datedeb, datefin) VALUES('" + label + "', '" + service + "', '" + nomEmp + "', '" + prenomEmp + "', '" + dateDeb + "', '" + dateFin + "')");
    }

    public Cursor getTaches(){
        return base.rawQuery("SELECT * FROM taches", null);
    }

    public Cursor getTacheNom(String nomTache){
        Log.i("BDD tache FIche", "SELECT * FROM taches WHERE nom='" + nomTache + "'");
        return base.rawQuery("SELECT * FROM taches WHERE nom='" + nomTache + "'", null);
    }

    public Cursor getTachesEmploye(String nom, String prenom){
        return base.rawQuery("SELECT datedeb, datefin FROM taches WHERE nomemploye='"+ nom +"' AND prenomemploye='" + prenom + "'", null);
    }

    public Cursor getAbsencesEmploye(String nom, String prenom){
        return base.rawQuery("SELECT datedeb, datefin FROM absences WHERE nomemploye='"+ nom +"' AND prenomemploye='" + prenom + "'", null);
    }

    public void insertAbsences(String nomEmp, String prenomEmp, String motif, String debut, String fin){
        base.execSQL("INSERT INTO absences(nomemploye, prenomemploye, raison, datedeb, datefin) VALUES('" + nomEmp + "', '" + prenomEmp + "', '" + motif + "', '" + debut + "', '" + fin + "');");
    }

    public Cursor getAbsences(){
        return base.rawQuery("SELECT * FROM absences", null);
    }

    public Cursor getAbsencesMotifNom(String motif, String nomEmp){
        return base.rawQuery("SELECT * FROM absences WHERE raison='" + motif + "' AND nomemploye='" + nomEmp + "'", null);
    }

    public Cursor getUneTache(){
        return base.rawQuery("SELECT * FROM uneTache", null);
    }

    public Cursor getAlerteNonValidee(String date){
        Log.i("testRequete", "SELECT * FROM uneTache WHERE valide = 0 AND date = '" + date + "'");
        return base.rawQuery("SELECT * FROM uneTache WHERE valide = 0 AND date = '" + date + "'", null);

    }

    public Cursor getAlerteUnJour(String jour){
        return base.rawQuery("SELECT * FROM listeTacheHebdo where jour ='" + jour + "'", null);
    }

    public Cursor getVerifAlerteExistante(int alerteHebdo, String date){
        return base.rawQuery("SELECT COUNT(*) FROM uneTache WHERE  date = '" + date + "' AND idtachehebdo = '" + alerteHebdo + "'", null);
    }

    public void ajouterUneTacheAlerte(int idTachHebdo, String date){
        base.execSQL("INSERT INTO uneTache(idtachehebdo, valide, date) VALUES (" + idTachHebdo + ",0, '" + date + "')");
    }
}
